﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Configuration;
using System.Text;
using System.Data;

/// <summary>
/// Summary description for QuestionnarieDAO
/// </summary>
public class QuestionnarieDAO : IQuestionnarieDAO
{
    private SqlConnection con = null;

    public QuestionnarieDAO()
    {
        ConnectionStringSettings MySettings =
            ConfigurationManager.ConnectionStrings["QuestionnarieConStringSQL"];
        con = new SqlConnection(MySettings.ConnectionString);
    }

    public Question RetrieveQuestion(int nrQuestion)
    {
        throw new NotImplementedException();
    }

    public Question[] RetrieveAllQuestions()
    {
        throw new NotImplementedException();
    }

    /// <summary>
    /// Adiciona questão de Múltipla Escolha ou Discursiva
    /// </summary>
    /// <param name="question"></param>
    /// <param name="typeQuestion"></param>
    /// <param name="questionnarieId"></param>
    public void AddQuestion(Question question, Question.TypeQuestion typeQuestion, int questionnarieId)
    {
        if (question != null)
        {
            Question Q = question;
            SqlCommand cmdInsertQuestion = new SqlCommand();
            SqlCommand cmdInsertChildren = new SqlCommand();
            StringBuilder sb = new StringBuilder();

            switch (typeQuestion)
            {
                case Question.TypeQuestion.Descriptive:
                    sb.Append("INSERT INTO DiscursiveQuestion (QuestionDescription, NrQuestion, QuestionnarieId)");
                    sb.Append(" VALUES ('" + question.QuestionDescription + "','" + question.NrQuestion + "','" + questionnarieId + "')");
                    cmdInsertQuestion.CommandText = sb.ToString();
                    cmdInsertQuestion.Connection = con;
                    con.Open();
                    using (con)
                    {
                        cmdInsertQuestion.ExecuteNonQuery();
                    }
                    break;
                case Question.TypeQuestion.MultipleChoice:
                    sb.Append("INSERT INTO MultipleChoiceQuestion (QuestionDescription, NrQuestion, QuestionnarieId)");
                    sb.Append(" VALUES ('" + question.QuestionDescription + "','" + question.NrQuestion + "','" + questionnarieId + "');");
                    sb.Append(" SELECT @@identity;");
                    cmdInsertQuestion.CommandText = sb.ToString();
                    cmdInsertQuestion.Connection = con;
                    con.Open();
                    using (con)
                    {
                        int id = Convert.ToInt32(cmdInsertQuestion.ExecuteScalar().ToString());
                        foreach (string s in question.Answer)
                        {
                            sb.Clear();
                            sb.Append("INSERT INTO MultipleChoiceQuestionItem (QuestionId, QuestionItemDescription)");
                            sb.Append(" VALUES ('" + id + "','" + s + "')");
                            cmdInsertChildren.CommandText = sb.ToString();
                            cmdInsertChildren.Connection = con;
                            cmdInsertChildren.ExecuteNonQuery();
                        }
                    }
                    break;
            }
        }
    }

    public void RemoveQuestion(int nrQuestion)
    {
        throw new NotImplementedException();
    }

    public void UpdateQuestion(Question question, int nrQuestion)
    {
        throw new NotImplementedException();
    }

    public void CreateQuestionnarie(string questionarySummary)
    {
        con.Open();
        using (con)
        {
            SqlCommand cmdInsert = new SqlCommand("INSERT INTO Questionnarie (QuestionnarieSummary, QuestionnarieStatus) VALUES ('" + questionarySummary + "','C')", con);
            cmdInsert.ExecuteNonQuery();
        }
    }

    public Questionnaire[] RetrieveAllQuestionnaries()
    {
        DataTable dt = new DataTable();
        con.Open();
        using (con)
        {
            SqlCommand cmdSelect = new SqlCommand("SELECT QuestionnarieID, QuestionnarieSummary FROM Questionnarie WHERE QuestionnarieStatus='C'", con);
            SqlDataAdapter adp = new SqlDataAdapter(cmdSelect);
            adp.Fill(dt);
        }

        Questionnaire[] quest = new Questionnaire[dt.Rows.Count];
        int indice = 0;
        foreach (DataRow r in dt.Rows)
        {
            quest[indice] = new Questionnaire();
            quest[indice].Nrquestionnaire = r.Field<int>(0);
            quest[indice].Summary = r.Field<string>(1);
            indice++;
        }

        return quest;
    }
}